package jsyf.oa.dao.system;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.system.BigAchievementsEntity;
import jsyf.oa.entity.system.RegionAchivementsEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by Maqiuqiu on 2018/8/2.
 */
@Repository
public class RegionAchivementsDao extends BaseDao {

    public RegionAchivementsDao() {
        super.iBaseEntity = new RegionAchivementsEntity();
    }

    public JSONObject getBegionMonthByUserId(String userId) {
        StringBuffer sql = new StringBuffer();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        sql.append(" SELECT");
        sql.append(" BEGIN_MONTH as beginMonth");
        sql.append(" FROM");
        sql.append(" t_sys_region_achivements WHERE");
        sql.append(" USER_ID=? ");
        sql.append(" ORDER BY CREATE_TIME DESC LIMIT 0,1");
        return super.queryForJsonObject(sql.toString(), new Object[]{userId});
    }

    public List<RegionAchivementsEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<RegionAchivementsEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            RegionAchivementsEntity entity = (RegionAchivementsEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    public List<JSONObject> getLzUsers(String month) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "v_sys_user.id as userId,\n" +
                "\tv_sys_user.`NAME`,\n" +
                "\tv_sys_user.entryDate,\n" +
                "\tt_sys_user_leave.LEAVE_DATE,\n" +
                "  v_sys_user.roleName," +
                "v_sys_user.companyId as companyId\n" +
                "FROM\n" +
                "\tt_sys_user_leave\n" +
                "LEFT JOIN v_sys_user ON t_sys_user_leave.USER_ID = v_sys_user.id\n" +
                "WHERE\n" +
                "\tLEAVE_DATE  LIKE " + "'%" + month + "%' \n" +
                "\n" +
                "AND v_sys_user.roleName in (\"区域经理\",\"大区经理\",\"区域组长\",\"区域总监\",\"销售总监\",\"总经理\")\n" +
                "GROUP BY v_sys_user.id");
        return super.queryForJsonList(sql.toString());
    }

    public JSONObject isSale(String userId, String areaId) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        String  currentMonth = sdf.format(new Date());
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT" +
                " SALE_TARGET_NUM AS targetNum,\n" +
                "SALE_FINISH_NUM AS finishNum,\n" +
                "BEGIN_MONTH AS beginMonth,\n" +
                "\tMIDDLE_MONTH AS middleMonth,\n" +
                //  "left(t_sys_region_achivements.CREATE_TIME,4) as year,\n" +
                "\tEND_MONTH AS endMonth\n");
        sql.append(" FROM t_sys_region_achivements\n" +
                " WHERE ? IN(BEGIN_MONTH,MIDDLE_MONTH,END_MONTH)\n");
        sql.append(" AND USER_ID=? AND AREA_ID=?");
        return super.queryForJsonObject(sql.toString(), new Object[]{currentMonth,userId, areaId});
    }

    public JSONObject check(String userId, String bigRegionId,int currentMonth,int currentYear) {
        StringBuffer sql = new StringBuffer();
        String beginMonth="";
        if (currentMonth<10){
            beginMonth=String.valueOf(currentYear)+"-0"+currentMonth;
        }else {
            beginMonth=String.valueOf(currentYear)+"-"+currentMonth;
        }
        sql.append(" SELECT\n" +
                "\tSALE_TARGET_NUM AS saleTargetNum,\n" +
                "\tID AS id,\n" +
                "\tAREA_ID AS areaId,\n" +
                "\tEND_MONTH AS endMonth,\n" +
                "\tCREATE_USER_ID AS createUserId,\n" +
                "\tCREATE_TIME AS createTime,\n" +
                "\tBEGIN_MONTH AS beginMonth,\n" +
                "\tMIDDLE_MONTH AS middleMonth,\n" +
                "\tSALE_FINISH_NUM AS saleFinishNum,\n" +
                "\tUSER_ID AS userId\n" +
                "FROM\n" +
                "\tt_sys_region_achivements\n" +
                "WHERE\n" +
                "\tUSER_ID=? AND AREA_ID=? AND ? IN(BEGIN_MONTH,MIDDLE_MONTH,END_MONTH)");
        return super.queryForJsonObject(sql.toString(), new Object[]{userId, bigRegionId, beginMonth});
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, RegionAchivementsEntity.class);
    }

    public List<JSONObject> getAreaByUserId(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ID as id,USER_ID AS userId,AREA_ID AS areaId");
        sql.append(" FROM");
        sql.append(" t_sys_region_achivements");
        sql.append(" WHERE USER_ID =?");
        return super.queryForJsonList(sql.toString(), new Object[]{userId});
    }

    public List<JSONObject> getSale(String companyId, String dateTime, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "userMain.companyId,\n" +
                "userMain.companyName,\n" +
                "userMain.regionName,\n" +
                "userMain.roleName,\n" +
                "userMain.userId,\n" +
                "userMain.userName,\n" +
                "'" + month + "' as cycle,\n" +
                "IFNULL(userMain.targetNum,0) as targetNum,\n" +
                "IFNULL(userMain.finishNum,0) as finishNum,\n" +
                "userMain.sortNo,\n" +
                "IFNULL(\n" +
                "\t\t-t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "\t(\n" +
                "\t\tIFNULL(\n" +
                "\t\t\tt_sys_bonus.SALE_REWARD,\n" +
                "\t\t\t0\n" +
                "\t\t) + IFNULL(-t_sys_bonus.SALE_PUNISHMENT, 0)\n" +
                "\t) AS money\n" +
                "FROM (");
        sb.append(" SELECT\n" +
                "\tv_sys_user.companyId,\n" +
                "\tv_sys_user.companyName,\n" +
                "\tv_sys_user.departName AS regionName,\n" +
                "\tv_sys_user.roleName,\n" +
                "\tv_sys_user.userId,\n" +
                "\tv_sys_user.userName,\n" +
                "\t'" + month + "' AS cycle,\n" +
                "\tv_sys_region.targetNum,\n" +
                "\tv_sys_region.finishNum,v_sys_user.sortNo\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_company.`ID` AS companyId,\n" +
                "\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\t\tt_sys_role_name.`NAME` AS roleName,\n" +
                "\t\t\tt_sys_role_name.`SORT_NO` AS sortNo,\n" +
                "\t\t\tt_sys_user.`ID` AS userId,\n" +
                "\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\tt_sys_user.`JOB_STATE` AS jobstate\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tUNION ALL\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_sys_company.`ID` AS companyId,\n" +
                "\t\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\t\t\tt_sys_role_name.`NAME` AS roleName,\n" +
                "\t\t\t\tt_sys_role_name.`SORT_NO` AS sortNo,\n" +
                "\t\t\t\tt_sys_user.`ID` AS userId,\n" +
                "\t\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\t\tt_sys_user.`JOB_STATE` AS jobstate\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_user_2_role\n" +
                "\t\t\tLEFT JOIN t_sys_user ON t_sys_user_2_role.USER_ID = t_sys_user.ID\n" +
                "\t\t\tLEFT JOIN t_sys_role ON t_sys_user_2_role.ROLE_ID = t_sys_role.ID\n" +
                "\t\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\t\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\t\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t) AS v_sys_user\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user_2_region.USER_ID,\n" +
                "\t\tSUM(\n" +
                "\t\t\t IFNULL(t_sys_big_achievements.SALE_TARGET_NUM,0)\n" +
                "\t\t) AS targetNum,\n" +
                "\t\tSUM(\n" +
                "\t\t\t IFNULL(t_sys_big_achievements.SALE_FINISH_NUM,0)\n" +
                "\t\t) AS finishNum\n" +
                "\tFROM\n" +
                "\t\tt_sys_user_2_region\n" +
                "\tLEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "\tLEFT JOIN t_sys_big_achievements ON t_sys_region.ID = t_sys_big_achievements.BIG_AREA_ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_region.PARENT_ID = 0\n" +
                "\t\tAND t_sys_region.COMPANY_ID='" + companyId + "'\n" +
                " AND t_sys_big_achievements.`MONTH` LIKE '%" + dateTime + "%' " +
                "\tGROUP BY\n" +
                "\t\tt_sys_user_2_region.USER_ID\n" +
                ") AS v_sys_region ON v_sys_user.userId = v_sys_region.USER_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND v_sys_user.jobstate != 1\n" +
                "AND v_sys_user.roleName IN (\n" +
                "\t'销售总监',\n" +
                "\t'区域总监'\n" +
                ")\n" +
                "AND v_sys_user.companyId = '" + companyId + "'\n" +
                "ORDER BY\n" +
                "\tv_sys_user.sortNo");
        sb.append(" ) as userMain\n" +
                "LEFT JOIN t_sys_bonus ON t_sys_bonus.USER_ID=userMain.userId AND t_sys_bonus.TYPE=1\n" +
                "AND t_sys_bonus.COMPANY_ID=userMain.companyId AND t_sys_bonus.BIG_REGION_ID is null\n" +
                " AND ROLE_NAME in('销售总监',\n" +
                "\t'区域总监') AND t_sys_bonus.`DAY` LIKE '%" + dateTime + "%' ORDER BY\n" +
                "\tuserMain.sortNo");

        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getNewSale(String companyId, String dateTime, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "\tt_sys_bonus.COMPANY_ID AS companyId,\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tNAME\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_company\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_company.id = '" + companyId + "'\n" +
                "\t) AS companyName,\n" +
                "\tt_sys_company.`NAME` AS regionName,\n" +
                "\tt_sys_user.`NAME` AS userName,\n" +
                "\tt_sys_user.ID AS userId,\n" +
                "\t'" + month + "' AS cycle,\n" +
                "\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\tt_sys_bonus.ROLE_NAME as roleName,\n" +
                "IFNULL(t_sys_bonus.SALE_TARGET_NUM, 0) AS targetNum,\n" +
                "\tIFNULL(t_sys_bonus.SALE_FINISH_NUM, 0) AS finishNum," +
                "\tIFNULL(\n" +
                "\t\t- t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "\t(\n" +
                "\t\tIFNULL(t_sys_bonus.SALE_REWARD, 0) + IFNULL(\n" +
                "\t\t\t- t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t\t0\n" +
                "\t\t)\n" +
                "\t) AS money\n" +
                "FROM\n" +
                "\t`t_sys_bonus`\n" +
                "LEFT JOIN t_sys_user ON t_sys_bonus.USER_ID = t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.id = t_sys_bonus.BIG_REGION_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "LEFT JOIN t_sys_big_achievements ON t_sys_big_achievements.BIG_AREA_ID = t_sys_bonus.BIG_REGION_ID\n" +
                "AND t_sys_big_achievements.`MONTH` = '" + dateTime + "'\n" +
                "WHERE\n" +
                "\tt_sys_bonus.COMPANY_ID = '" + companyId + "'\n" +
                "AND t_sys_bonus.TYPE = 1\n" +
                "AND LEFT (DAY, 7) = '" + dateTime + "'\n" +
                "AND t_sys_bonus.ROLE_NAME in(\"销售总监\",\"区域总监\")\n" +
                "ORDER BY\n" +
                "\tt_sys_role_name.SORT_NO");

        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getSaleBigManagerList(String companyId, String dateTime, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "userMain.companyId,\n" +
                "userMain.companyName,\n" +
                "userMain.regionName,\n" +
                "userMain.roleName,\n" +
                "userMain.userId,\n" +
                "userMain.userName,\n" +
                "'" + month + "' as cycle,\n" +
                "IFNULL(userMain.targetNum,0) as targetNum,\n" +
                "IFNULL(userMain.finishNum,0) as finishNum,\n" +
                "userMain.regionId,\n" +
                "userMain.regionSortNo,\n" +
                "IFNULL(\n" +
                "\t\t-t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "\t(\n" +
                "\t\tIFNULL(\n" +
                "\t\t\tt_sys_bonus.SALE_REWARD,\n" +
                "\t\t\t0\n" +
                "\t\t) + IFNULL(-t_sys_bonus.SALE_PUNISHMENT, 0)\n" +
                "\t) AS money\n" +
                "FROM (");
        sb.append(" SELECT\n" +
                "\tv_sys_user.companyId,\n" +
                "\tv_sys_user.companyName,\n" +
                "\tv_sys_user.departName AS regionName,\n" +
                "\tv_sys_user.roleName,\n" +
                "\tv_sys_user.userId,\n" +
                "\tv_sys_user.userName,\n" +
                "\t'" + month + "' AS cycle,\n" +
                "\tv_sys_region.targetNum,\n" +
                "\tv_sys_region.finishNum,v_sys_region.regionId,\n" +
                "v_sys_region.regionSortNo\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_company.`ID` AS companyId,\n" +
                "\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\t\tt_sys_role_name.`NAME` AS roleName,\n" +
                "\t\t\tt_sys_role_name.`SORT_NO` AS sortNo,\n" +
                "\t\t\tt_sys_user.`ID` AS userId,\n" +
                "\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\tt_sys_user.`JOB_STATE` AS jobstate\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tUNION ALL\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_sys_company.`ID` AS companyId,\n" +
                "\t\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\t\t\tt_sys_role_name.`NAME` AS roleName,\n" +
                "\t\t\t\tt_sys_role_name.`SORT_NO` AS sortNo,\n" +
                "\t\t\t\tt_sys_user.`ID` AS userId,\n" +
                "\t\t\t\tt_sys_user.`NAME` AS userName,\n" +
                "\t\t\t\tt_sys_user.`JOB_STATE` AS jobstate\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_user_2_role\n" +
                "\t\t\tLEFT JOIN t_sys_user ON t_sys_user_2_role.USER_ID = t_sys_user.ID\n" +
                "\t\t\tLEFT JOIN t_sys_role ON t_sys_user_2_role.ROLE_ID = t_sys_role.ID\n" +
                "\t\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\t\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\t\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t) AS v_sys_user\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\t    t_sys_region.ID as regionId,t_sys_region.`NAME`,\n" +
                "\t\tt_sys_region.SORT_NO AS regionSortNo,\n" +
                "\t\t\n" +
                "\t\t\t IFNULL(t_sys_big_achievements.SALE_TARGET_NUM,0)\n" +
                "\t\t AS targetNum,\n" +
                "\t\t\n" +
                "\t\t\t IFNULL(t_sys_big_achievements.SALE_FINISH_NUM,0)\n" +
                "\t\t AS finishNum\n" +
                "\tFROM\n" +
                "\t\tt_sys_user_2_region\n" +
                "\tLEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "\tLEFT JOIN t_sys_big_achievements ON t_sys_region.ID = t_sys_big_achievements.BIG_AREA_ID\n" +
                "\tWHERE\n" +
                "\t\tt_sys_region.PARENT_ID = 0\n" +
                "\t\tAND t_sys_region.COMPANY_ID='" + companyId + "'\n" +
                " AND t_sys_big_achievements.`MONTH` LIKE '%" + dateTime + "%' " +
                "\tGROUP BY\n" +
                "\t\tt_sys_region.`NAME`\n" +
                ") AS v_sys_region ON v_sys_user.departName = v_sys_region.`NAME`\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND v_sys_user.jobstate != 1\n" +
                "AND v_sys_user.roleName IN (\n" +
                "\t'大区经理'\n" +
                ")\n" +
                "AND v_sys_user.companyId = '" + companyId + "'\n" +
                "ORDER BY\n" +
                "\tv_sys_user.sortNo,v_sys_region.regionSortNo");
        sb.append(" ) as userMain\n" +
                "LEFT JOIN t_sys_bonus ON t_sys_bonus.BIG_REGION_ID=userMain.regionId AND t_sys_bonus.TYPE=1\n" +
                "AND t_sys_bonus.USER_ID=userMain.userId\n" +
                " AND t_sys_bonus.ROLE_NAME=\"大区经理\" AND t_sys_bonus.`DAY` LIKE '%" + dateTime + "%' ORDER BY userMain.regionSortNo");
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getNewSaleBigManagerList(String companyId, String dateTime, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "  t_sys_bonus.COMPANY_ID as companyId,\n" +
                "  (SELECT name from t_sys_company WHERE t_sys_company.id='" + companyId + "') as companyName,\n" +
                "  t_sys_region.`NAME` as regionName,\n" +
                "\tt_sys_user.`NAME` as userName,\n" +
                "t_sys_user.ID as userId,\n" +
                "'" + month + "' as cycle,\n" +
                "t_sys_region.SORT_NO as regionSortNo,\n" +
                "  t_sys_bonus.ROLE_NAME as roleName,\n" +
                " IFNULL(t_sys_big_achievements.SALE_TARGET_NUM,0) as targetNum,\n" +
                "IFNULL(t_sys_big_achievements.SALE_FINISH_NUM,0) as finishNum," +
                "\n" +
                "  IFNULL(\n" +
                "\t\t-t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "(\n" +
                "\t\tIFNULL(\n" +
                "\t\t\tt_sys_bonus.SALE_REWARD,\n" +
                "\t\t\t0\n" +
                "\t\t) + IFNULL(-t_sys_bonus.SALE_PUNISHMENT, 0)\n" +
                "\t) AS money\n" +
                "FROM\n" +
                "\t`t_sys_bonus` \n" +
                "LEFT JOIN t_sys_user ON t_sys_bonus.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.id=t_sys_bonus.BIG_REGION_ID \n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID=t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID=t_sys_company.ID\n" +
                "LEFT JOIN t_sys_big_achievements ON t_sys_big_achievements.BIG_AREA_ID=t_sys_bonus.BIG_REGION_ID\n" +
                "AND t_sys_big_achievements.`MONTH`='" + dateTime + "'\n" +
                "WHERE t_sys_bonus.COMPANY_ID='" + companyId + "'\n" +
                "AND t_sys_bonus.TYPE=1 AND left(day,7)='" + dateTime + "'\n" +
                "AND t_sys_bonus.ROLE_NAME=\"大区经理\"\n" +
                " ORDER BY t_sys_region.SORT_NO");
        //不需要查没有配置区域的区域经理。因为bonous 大区经理的数据是通过部门下边的区域经理计算的，已经有数据了。
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getNewSaleSmallManagerList(String companyId, String dateTime, String month) {
        StringBuffer sb = new StringBuffer();
        sb.append("  SELECT\n" +
                "  t_sys_bonus.COMPANY_ID as companyId,\n" +
                "  (SELECT name from t_sys_company WHERE t_sys_company.id='" + companyId + "') as companyName,\n" +
                "  region.`NAME` AS regionName,\n" +
                "\tt_sys_user.`NAME` as userName,\n" +
                "t_sys_user.ID as userId,\n" +
                "CONCAT(t_sys_region_achivements.BEGIN_MONTH,'-',t_sys_region_achivements.END_MONTH) AS cycle,\n" +
                " IFNULL(t_sys_bonus.SALE_TARGET_NUM,0) as targetNum,\n" +
                " IFNULL(t_sys_bonus.SALE_FINISH_NUM,0) as finishNum,\n" +
                "  t_sys_bonus.ROLE_NAME as roleName,\n" +
                "  IFNULL(\n" +
                "\t\t-t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "(\n" +
                "\t\tIFNULL(\n" +
                "\t\t\tt_sys_bonus.SALE_REWARD,\n" +
                "\t\t\t0\n" +
                "\t\t) + IFNULL(-t_sys_bonus.SALE_PUNISHMENT, 0)\n" +
                "\t) AS money\n" +
                "FROM\n" +
                "\t`t_sys_bonus` \n" +
                "LEFT JOIN t_sys_user ON t_sys_bonus.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_bonus.USER_ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.id=t_sys_user_2_region.REGION_ID \n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_region.COMPANY_ID=t_sys_company.ID\n" +
                "LEFT JOIN t_sys_region_achivements ON t_sys_region_achivements.USER_ID=t_sys_bonus.USER_ID\n" +
                "AND t_sys_region_achivements.AREA_ID=t_sys_bonus.BIG_REGION_ID\n" +
                "LEFT JOIN(SELECT t_sys_region.`NAME`,t_sys_bonus.BIG_REGION_ID AS regionId FROM t_sys_region \n" +
                "   LEFT JOIN t_sys_bonus ON t_sys_bonus.BIG_REGION_ID = t_sys_region.ID\n" +
                "   ) AS region ON region.regionId = t_sys_bonus.BIG_REGION_ID\n" +
                "WHERE t_sys_bonus.COMPANY_ID='" + companyId + "'\n" +
                "AND t_sys_bonus.COMPANY_ID=t_sys_region.COMPANY_ID\n" +
                "AND t_sys_bonus.TYPE=1 AND left(day,7)='" + dateTime + "'\n" +
                "AND '"+dateTime+"' in(t_sys_region_achivements.BEGIN_MONTH,t_sys_region_achivements.MIDDLE_MONTH,t_sys_region_achivements.END_MONTH)\n"+
                "AND t_sys_bonus.ROLE_NAME=\"区域组长\"\n" +
                "AND t_sys_region.PARENT_ID!=0" +
                " GROUP BY t_sys_bonus.USER_ID\n" +
                "\n");

        sb.append(" UNION ALL SELECT\n" +
                "\tv_sys_user.companyId,\n" +
                "\tv_sys_user.companyName,\n" +
                "\t\"\" as regionName,\n" +
                "\tv_sys_user.name as userName,\n" +
                "\tv_sys_user.id as userId,\n" +
                "\t\"\" as cycle,\n" +
                "\t0 as targetNum,\n" +
                "\t0 as finishNum,\n" +
                "\t\"区域组长\" as roleName,\n" +
                "\t0 as salePunishment,\n" +
                "\t0 as saleReward,\n" +
                "\t0 as money\n" +
                "FROM\n" +
                "\tv_sys_user\n" +
                "WHERE\n" +
                "\tid NOT IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID = t_sys_user.ID\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_region.COMPANY_ID = '" + companyId + "'\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tt_sys_user.ID\n" +
                "\t)\n" +
                "AND companyId = '" + companyId + "'\n" +
                "AND jobState != 1\n" +
                "AND roleName = \"区域组长\"\n" +
                "\n");

        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getSaleManagerList(String companyId, String dateTime, String month) {
        StringBuffer sb = new StringBuffer();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        String currentMonth = sdf.format(new Date());
        sb.append(" SELECT\n" +
                "\tv_sys_user.companyId,\n" +
                "\tv_sys_user.userId,\n" +
                "\tv_sys_user.userName,\n" +
                "\tv_sys_user.companyName,\n" +
                "\tv_sys_user.roleName,\n" +
                "\tv_sys_region.regionName,\n" +
                //       "\tt_sys_region_achivements.BEGIN_MONTH AS cycle,\n"+
                "\tCONCAT(t_sys_region_achivements.BEGIN_MONTH,'-',t_sys_region_achivements.END_MONTH) AS cycle,\n" +
                "\tIFNULL(\n" +
                "\t\tt_sys_region_achivements.SALE_FINISH_NUM,\n" +
                "\t\t0\n" +
                "\t) AS finishNum,\n" +
                "\tIFNULL(\n" +
                "\t\tt_sys_region_achivements.SALE_TARGET_NUM,\n" +
                "\t\t0\n" +
                "\t) AS targetNum,\n" +
                "\tIFNULL(\n" +
                "\t\t- t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "\t(\n" +
                "\t\tIFNULL(t_sys_bonus.SALE_REWARD, 0) + IFNULL(\n" +
                "\t\t\t- t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t\t0\n" +
                "\t\t)\n" +
                "\t) AS money\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tv_sys_part_time_for_report.companyId,\n" +
                "\t\t\tv_sys_part_time_for_report.roleNameId,\n" +
                "\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\tt_sys_role_name.`NAME` AS roleName,\n" +
                "\t\t\tv_sys_part_time_for_report.userId,\n" +
                "\t\t\tv_sys_part_time_for_report.userName\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_part_time_for_report\n" +
                "\t\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_company.ID = v_sys_part_time_for_report.companyId\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND v_sys_part_time_for_report.companyId = '" + companyId + "'\n" +
                "\t\tAND t_sys_role_name.DEL_FLAG = 0\n" +
                "\t\tAND v_sys_part_time_for_report.jobState != 1\n" +
                "\t\tAND t_sys_role_name.`NAME` = '区域经理'" +
                " AND v_sys_part_time_for_report.userId NOT IN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user.id\n" +
                "\tFROM\n" +
                "\t\tt_sys_user\n" +
                "\tWHERE\n" +
                "\t\tLEFT (t_sys_user.ENTRY_DATE, 7) = '" + dateTime + "' AND RIGHT(t_sys_user.ENTRY_DATE, 2)>15\n" +
                ")\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tv_sys_part_time_for_report.userId\n" +
                "\t) AS v_sys_user\n" +
                "LEFT JOIN t_sys_region_achivements ON v_sys_user.userId = t_sys_region_achivements.USER_ID\n");
        sb.append(" AND AREA_ID in (SELECT\n" +
                "t_sys_region.id as regionId\n" +
                "\tFROM\n" +
                "\t\tt_sys_user_2_region\n" +
                "\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\tAND t_sys_region.PARENT_ID= 0\n" +
                "\tAND t_sys_region.COMPANY_ID = '" + companyId + "'\n" +
                "\tGROUP BY\n" +
                "\t\tt_sys_user_2_region.USER_ID\n" +
                "\tORDER BY\n" +
                "\t\tt_sys_region.SORT_NO )");
        sb.append("AND (\n" +
                "\tt_sys_region_achivements.BEGIN_MONTH = '" + month + "'\n" +
                "\tOR t_sys_region_achivements.MIDDLE_MONTH = '" + month + "'\n" +
                "\tOR t_sys_region_achivements.END_MONTH = '" + month + "'\n" +
                ")\n" +
                "LEFT JOIN t_sys_bonus ON v_sys_user.userId = t_sys_bonus.USER_ID" +
                " AND t_sys_bonus.COMPANY_ID=v_sys_user.companyId AND t_sys_bonus.ROLE_NAME=\"区域经理\"\n" +
                "AND t_sys_bonus.`DAY` LIKE '%" + dateTime + "%' and t_sys_bonus.TYPE=1\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_user_2_region.USER_ID AS userId,\n" +
                "\t\tt_sys_region.`NAME` AS regionName\n" +
                "\tFROM\n" +
                "\t\tt_sys_user_2_region\n" +
                "\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\tAND t_sys_region.PARENT_ID != 0\n" +
                "\tAND t_sys_region.COMPANY_ID = '" + companyId + "'\n" +
                "\tGROUP BY\n" +
                "\t\tt_sys_user_2_region.USER_ID\n" +
                "\tORDER BY\n" +
                "\t\tt_sys_region.SORT_NO\n" +
                ") AS v_sys_region ON v_sys_region.userId = v_sys_user.userId\n" +
                "GROUP BY\n" +
                "\tv_sys_user.userId ");

        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getNewSaleManagerList(String companyId, String dateTime, String month) {
        StringBuffer sb = new StringBuffer();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        String currentMonth = sdf.format(new Date());
        sb.append("  SELECT\n" +
                "  t_sys_bonus.COMPANY_ID as companyId,\n" +
                "  (SELECT name from t_sys_company WHERE t_sys_company.id='" + companyId + "') as companyName,\n" +
                "  t_sys_region.`NAME` as regionName,\n" +
                "\tt_sys_user.`NAME` as userName,\n" +
                "t_sys_user.ID as userId,\n" +
                "CONCAT(\n" +
                "\tCASE WHEN SUBSTRING(t_sys_region_achivements.BEGIN_MONTH,6,1)='0'\n" +
                "   THEN SUBSTRING(t_sys_region_achivements.BEGIN_MONTH,7,1)\n" +
                "     ELSE SUBSTRING(t_sys_region_achivements.BEGIN_MONTH,6,2)\n" +
                "       END,\n" +
                "\t\t'-',\n" +
                "\tCASE WHEN SUBSTRING(t_sys_region_achivements.END_MONTH,6,1)='0'\n" +
                "    THEN SUBSTRING(t_sys_region_achivements.END_MONTH,7,1)\n" +
                "      ELSE SUBSTRING(t_sys_region_achivements.END_MONTH,6,2)\n" +
                "       END \n" +
                "\t) AS cycle,\n" +
                " IFNULL(t_sys_bonus.SALE_TARGET_NUM,0) as targetNum,\n" +
                " IFNULL(t_sys_bonus.SALE_FINISH_NUM,0) as finishNum,\n" +
                "  t_sys_bonus.ROLE_NAME as roleName,\n" +
                "  IFNULL(\n" +
                "\t\t-t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "(\n" +
                "\t\tIFNULL(\n" +
                "\t\t\tt_sys_bonus.SALE_REWARD,\n" +
                "\t\t\t0\n" +
                "\t\t) + IFNULL(-t_sys_bonus.SALE_PUNISHMENT, 0)\n" +
                "\t) AS money\n" +
                "FROM\n" +
                "\t`t_sys_bonus` \n" +
                "LEFT JOIN t_sys_user ON t_sys_bonus.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_bonus.USER_ID=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.id=t_sys_user_2_region.REGION_ID \n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_region.COMPANY_ID=t_sys_company.ID\n" +
                "LEFT JOIN t_sys_region_achivements ON t_sys_region_achivements.USER_ID=t_sys_bonus.USER_ID\n" +
                "AND t_sys_region_achivements.AREA_ID=t_sys_bonus.BIG_REGION_ID\n" +
                "WHERE t_sys_bonus.COMPANY_ID='" + companyId + "'\n" +
                "AND t_sys_bonus.COMPANY_ID=t_sys_region.COMPANY_ID\n" +
                "AND t_sys_bonus.TYPE=1 AND left(day,7)='" + dateTime + "'\n" +
                "AND '"+dateTime+"' in(t_sys_region_achivements.BEGIN_MONTH,t_sys_region_achivements.MIDDLE_MONTH,t_sys_region_achivements.END_MONTH)\n"+
                "AND t_sys_bonus.ROLE_NAME=\"区域经理\"\n" +
                "AND t_sys_region.PARENT_ID!=0" +
                " GROUP BY t_sys_bonus.USER_ID\n" +
                "\n");

        sb.append(" UNION ALL SELECT\n" +
                "\tv_sys_user.companyId,\n" +
                "\tv_sys_user.companyName,\n" +
                "\t\"\" as regionName,\n" +
                "\tv_sys_user.name as userName,\n" +
                "\tv_sys_user.id as userId,\n" +
                "\t\"\" as cycle,\n" +
                "\t0 as targetNum,\n" +
                "\t0 as finishNum,\n" +
                "\t\"区域经理\" as roleName,\n" +
                "\t0 as salePunishment,\n" +
                "\t0 as saleReward,\n" +
                "\t0 as money\n" +
                "FROM\n" +
                "\tv_sys_user\n" +
                "WHERE\n" +
                "\tid NOT IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID = t_sys_user.ID\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_region.COMPANY_ID = '" + companyId + "'\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tt_sys_user.ID\n" +
                "\t)\n" +
                "AND companyId = '" + companyId + "'\n" +
                "AND jobState != 1\n" +
                "AND roleName = \"区域经理\"\n" +
                "\n");
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getNewManagerSale(String dateTime, String condition) {
        String month = "";
        if (dateTime.substring(5, 7).startsWith("0")) {
            month = dateTime.substring(6);
        } else {
            month = dateTime.substring(5, 7);
        }
        StringBuffer sb = new StringBuffer();
        sb.append("  select * from (");
        sb.append("  SELECT\n" +
                "\tt_sys_bonus.COMPANY_ID AS companyId,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "\tt_sys_company.`NAME` AS regionName,\n" +
                "\tt_sys_user.`NAME` as userName,\n" +
                "\t'" + month + "' AS cycle,\n" +
                "\tt_sys_bonus.ROLE_NAME as roleName,\n" +
                "IFNULL(t_sys_bonus.SALE_TARGET_NUM, 0) AS targetNum,\n" +
                "\tIFNULL(t_sys_bonus.SALE_FINISH_NUM, 0) AS finishNum,\tIFNULL(\n" +
                "\t\t- t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "\t(\n" +
                "\t\tIFNULL(t_sys_bonus.SALE_REWARD, 0) + IFNULL(\n" +
                "\t\t\t- t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t\t0\n" +
                "\t\t)\n" +
                "\t) AS money,t_sys_company.SORT_NO as sortNo\n" +
                "FROM\n" +
                "\t`t_sys_company`\n" +
                "LEFT JOIN t_sys_bonus ON t_sys_bonus.COMPANY_ID = t_sys_company.ID\n" +
                " LEFT JOIN t_sys_user ON t_sys_bonus.USER_ID=t_sys_user.ID\n" +
                "WHERE\n" +
                "\t1=1\n" +
                "AND t_sys_bonus.TYPE = 1\n" +
                "AND LEFT (DAY, 7) = '" + dateTime + "'\n" +
                "AND t_sys_bonus.ROLE_NAME =\"总经理\"\n" +
                "AND t_sys_company.ID!='2017121910424ED9AA4F786D5549D72F'");
        sb.append(condition);
        sb.append(" UNION ALL SELECT t_sys_company.ID,t_sys_company.`NAME`,t_sys_company.`NAME`,\"\",'" + month + "','总经理'," +
                " (SELECT \n" +
                "         IFNULL(sum(t_sys_bonus.SALE_TARGET_NUM),0)\n" +
                "         from t_sys_bonus \n" +
                "        WHERE t_sys_bonus.TYPE=1 AND LEFT (t_sys_bonus.`DAY`, 7) = '" + dateTime + "' AND t_sys_bonus.COMPANY_ID =t_sys_company.ID AND t_sys_bonus.ROLE_NAME=\"大区经理\"),\n" +
                "         (SELECT \n" +
                "         IFNULL(sum(t_sys_bonus.SALE_FINISH_NUM),0)\n" +
                "         from t_sys_bonus \n" +
                "        WHERE t_sys_bonus.TYPE=1 AND LEFT (t_sys_bonus.`DAY`, 7) = '" + dateTime + "' AND t_sys_bonus.COMPANY_ID =t_sys_company.ID AND t_sys_bonus.ROLE_NAME=\"大区经理\"),\t\t\t\t" +
                "0,0,0,t_sys_company.SORT_NO\n" +
                "FROM t_sys_company WHERE ID not in(select t_sys_bonus.COMPANY_ID from t_sys_bonus where type=1 AND LEFT (DAY, 7) = '" + dateTime + "' AND t_sys_bonus.ROLE_NAME=\"总经理\" GROUP BY t_sys_bonus.COMPANY_ID)\n" +
                "AND t_sys_company.DEL_FLAG=0 AND t_sys_company.`NAME` not in(\"滚滚向前集团\",\"重庆迎风\")");
        sb.append(" ) as allMsg ORDER BY allMsg.sortNo");

        /*sb.append("ORDER BY t_sys_company.SORT_NO");*/
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List<JSONObject> getManagerSale(String dateTime, String condition) {
        String month = "";
        if (dateTime.substring(5, 7).startsWith("0")) {
            month = dateTime.substring(6);
        } else {
            month = dateTime.substring(5, 7);
        }
        StringBuffer sb = new StringBuffer();
        sb.append(" (SELECT\n" +
                "\tuserMain.*,\n" +
                "   IFNULL(\n" +
                "\t\t-t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t0\n" +
                "\t) AS salePunishment,\n" +
                "\tIFNULL(t_sys_bonus.SALE_REWARD, 0) AS saleReward,\n" +
                "\t(\n" +
                "\t\tIFNULL(t_sys_bonus.SALE_REWARD, 0) + IFNULL(\n" +
                "\t\t\t-t_sys_bonus.SALE_PUNISHMENT,\n" +
                "\t\t\t0\n" +
                "\t\t)\n" +
                "\t) AS money\n" +
                "FROM (");


        sb.append(" SELECT\n" +
                "\tv_sys_main.companyId,v_sys_main.companyName," +
                "v_sys_main.sortNo,v_sys_main.roleName," +
                "v_sys_main.finishNum,v_sys_main.targetNum," +
                " v_sys_company.userName,\n" +
                "\tv_sys_company.userId,\n" +
                "\t'" + month + "' AS cycle\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tv_sys_comDep.companyId,\n" +
                "\t\t\tv_sys_comDep.companyName,\n" +
                "\t\t\tv_sys_comDep.sortNo,\n" +
                "\t\t\tv_sys_comDep.roleName,\n" +
                "\t\t\tv_sys_comDep.regionName,\n" +
                "\t\t\tv_sys_regionNum.finishNum,\n" +
                "\t\t\tv_sys_regionNum.targetNum\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_sys_company.ID AS companyId,\n" +
                "\t\t\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\t\t\t'总经理' AS roleName,\n" +
                "\t\t\t\t\tt_sys_company.`NAME` AS regionName,\n" +
                "\t\t\t\t\tt_sys_company.SORT_NO AS sortNo\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_sys_company\n" +
                "\t\t\t\tLEFT JOIN t_sys_depart ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "\t\t\t\tLEFT JOIN t_sys_role ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "\t\t\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND t_sys_company.PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA'\n" +
                "\t\t\t\tAND t_sys_company.ID != '2017121617484A2796D8F52B6B558B94'\n" +
                "\t\t\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\t\t\tAND t_sys_role_name.DEL_FLAG = 0\n" +
                "\t\t\t\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\t\t\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tt_sys_company.ID\n" +
                "\t\t\t\tORDER BY\n" +
                "\t\t\t\t\tt_sys_company.SORT_NO\n" +
                "\t\t\t) AS v_sys_comDep\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tSUM(IFNULL(v_sys_num.targetNum,0)) AS targetNum,\n" +
                "\t\t\t\tSUM(IFNULL(v_sys_num.finishNum,0)) AS finishNum,\n" +
                "\t\t\t\tt_sys_region.company_id AS companyId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\t(\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\tt_sys_big_achievements.BIG_AREA_ID AS regionId,\n" +
                "\t\t\t\t\t\tSUM(SALE_TARGET_NUM) AS targetNum,\n" +
                "\t\t\t\t\t\tSUM(SALE_FINISH_NUM) AS finishNum\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\tt_sys_big_achievements\n" +
                "\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t1 = 1\n" +
                "\t\t\t\t\tAND t_sys_big_achievements.BIG_AREA_ID IN (\n" +
                "\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\tt_sys_region.ID\n" +
                "\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\tt_sys_region\n" +
                "\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t1 = 1\n" +
                "\t\t\t\t\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\t\t\t\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\t\tAND t_sys_big_achievements.`MONTH` LIKE '%" + dateTime + "%'\n" +
                "\t\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\t\tt_sys_big_achievements.BIG_AREA_ID\n" +
                "\t\t\t\t) v_sys_num\n" +
                "\t\t\tLEFT JOIN t_sys_region ON v_sys_num.regionId = t_sys_region.ID\n" +
                "\t\t\tGROUP BY\n" +
                "\t\t\t\tt_sys_region.company_id\n" +
                "\t\t) AS v_sys_regionNum ON v_sys_comDep.companyId = v_sys_regionNum.companyId\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tv_sys_comDep.companyId\n" +
                "\t) AS v_sys_main\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tv_sys_part_time_for_report.companyId,\n" +
                "\t\tv_sys_part_time_for_report.userName,\n" +
                "\t\tv_sys_part_time_for_report.userId\n" +
                "\tFROM\n" +
                "\t\tv_sys_part_time_for_report\n" +
                "\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\tv_sys_part_time_for_report.jobState != 1\n" +
                "\tAND t_sys_role_name.`NAME` = '总经理'\n" +
                "\tGROUP BY\n" +
                "\t\tv_sys_part_time_for_report.companyId\n" +
                ") AS v_sys_company ON v_sys_company.companyId = v_sys_main.companyId\n" + " ");
        sb.append(condition);
        sb.append(" ) AS userMain\n" +
                "LEFT JOIN t_sys_bonus ON t_sys_bonus.USER_ID = userMain.userId AND t_sys_bonus.TYPE=1\n" +
                "AND t_sys_bonus.ROLE_NAME = \"总经理\"\n" +
                "AND t_sys_bonus.BIG_REGION_ID IS NULL AND t_sys_bonus.`DAY` LIKE '%" + dateTime + "%'\n" +
                "AND userMain.companyId = t_sys_bonus.COMPANY_ID ORDER BY userMain.sortNo)");
//合计
        sb.append(" UNION ALL (SELECT '','合计',99,'',sum(userMain.finishNum),sum(userMain.targetNum),'','',userMain.cycle,IFNULL(-SUM(SALE_PUNISHMENT),0),IFNULL(SUM(SALE_REWARD),0) ,IFNULL(IFNULL(-SUM(SALE_PUNISHMENT),0)+IFNULL(SUM(SALE_REWARD),0),0) FROM (");
        sb.append(" SELECT\n" +
                "\tv_sys_main.companyId,v_sys_main.companyName," +
                "v_sys_main.sortNo,v_sys_main.roleName," +
                "v_sys_main.finishNum,v_sys_main.targetNum," +
                " v_sys_company.userName,\n" +
                "\tv_sys_company.userId,\n" +
                "\t'" + month + "' AS cycle\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tv_sys_comDep.companyId,\n" +
                "\t\t\tv_sys_comDep.companyName,\n" +
                "\t\t\tv_sys_comDep.sortNo,\n" +
                "\t\t\tv_sys_comDep.roleName,\n" +
                "\t\t\tv_sys_comDep.regionName,\n" +
                "\t\t\tv_sys_regionNum.finishNum,\n" +
                "\t\t\tv_sys_regionNum.targetNum\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tt_sys_company.ID AS companyId,\n" +
                "\t\t\t\t\tt_sys_company.`NAME` AS companyName,\n" +
                "\t\t\t\t\t'总经理' AS roleName,\n" +
                "\t\t\t\t\tt_sys_company.`NAME` AS regionName,\n" +
                "\t\t\t\t\tt_sys_company.SORT_NO AS sortNo\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_sys_company\n" +
                "\t\t\t\tLEFT JOIN t_sys_depart ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "\t\t\t\tLEFT JOIN t_sys_role ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "\t\t\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND t_sys_company.PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA'\n" +
                "\t\t\t\tAND t_sys_company.ID != '2017121617484A2796D8F52B6B558B94'\n" +
                "\t\t\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\t\t\tAND t_sys_role_name.DEL_FLAG = 0\n" +
                "\t\t\t\tAND t_sys_role.DEL_FLAG = 0\n" +
                "\t\t\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\tt_sys_company.ID\n" +
                "\t\t\t\tORDER BY\n" +
                "\t\t\t\t\tt_sys_company.SORT_NO\n" +
                "\t\t\t) AS v_sys_comDep\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tSUM(IFNULL(v_sys_num.targetNum,0)) AS targetNum,\n" +
                "\t\t\t\tSUM(IFNULL(v_sys_num.finishNum,0)) AS finishNum,\n" +
                "\t\t\t\tt_sys_region.company_id AS companyId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\t(\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\tt_sys_big_achievements.BIG_AREA_ID AS regionId,\n" +
                "\t\t\t\t\t\tSUM(SALE_TARGET_NUM) AS targetNum,\n" +
                "\t\t\t\t\t\tSUM(SALE_FINISH_NUM) AS finishNum\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\tt_sys_big_achievements\n" +
                "\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t1 = 1\n" +
                "\t\t\t\t\tAND t_sys_big_achievements.BIG_AREA_ID IN (\n" +
                "\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\tt_sys_region.ID\n" +
                "\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\tt_sys_region\n" +
                "\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t1 = 1\n" +
                "\t\t\t\t\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\t\t\t\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\t\tAND t_sys_big_achievements.`MONTH` LIKE '%" + dateTime + "%'\n" +
                "\t\t\t\t\tGROUP BY\n" +
                "\t\t\t\t\t\tt_sys_big_achievements.BIG_AREA_ID\n" +
                "\t\t\t\t) v_sys_num\n" +
                "\t\t\tLEFT JOIN t_sys_region ON v_sys_num.regionId = t_sys_region.ID\n" +
                "\t\t\tGROUP BY\n" +
                "\t\t\t\tt_sys_region.company_id\n" +
                "\t\t) AS v_sys_regionNum ON v_sys_comDep.companyId = v_sys_regionNum.companyId\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tv_sys_comDep.companyId\n" +
                "\t) AS v_sys_main\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tv_sys_part_time_for_report.companyId,\n" +
                "\t\tv_sys_part_time_for_report.userName,\n" +
                "\t\tv_sys_part_time_for_report.userId\n" +
                "\tFROM\n" +
                "\t\tv_sys_part_time_for_report\n" +
                "\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\tWHERE\n" +
                "\t\tv_sys_part_time_for_report.jobState != 1\n" +
                "\tAND t_sys_role_name.`NAME` = '总经理'\n" +
                "\tGROUP BY\n" +
                "\t\tv_sys_part_time_for_report.companyId\n" +
                ") AS v_sys_company ON v_sys_company.companyId = v_sys_main.companyId\n" + " ");
        sb.append(condition);
        sb.append(" ) AS userMain\n" +
                "LEFT JOIN t_sys_bonus ON t_sys_bonus.USER_ID = userMain.userId AND t_sys_bonus.TYPE=1\n" +
                "AND t_sys_bonus.ROLE_NAME = \"总经理\"\n" +
                "AND t_sys_bonus.BIG_REGION_ID IS NULL AND t_sys_bonus.`DAY` LIKE '%" + dateTime + "%'\n" +
                "AND userMain.companyId = t_sys_bonus.COMPANY_ID ORDER BY userMain.sortNo)");

        sb.append(" ORDER BY sortNo");
        List<JSONObject> list = super.queryForJsonList(sb.toString());
        return list;
    }

    public List getCountRegionMonthList(String  month) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT USER_ID as id,");
        sql.append(" AREA_ID as departId,");
        sql.append(" BEGIN_MONTH as beginMonth,");
        sql.append(" MIDDLE_MONTH as middleMonth,");
        sql.append(" END_MONTH AS endMonth");
        sql.append(" FROM");
        sql.append(" t_sys_region_achivements");
        sql.append(" WHERE ?=END_MONTH");
        return super.queryForJsonList(sql.toString(), new Object[]{});
    }
    public List<JSONObject> getDeviceType(String userId) {
        StringBuffer sql = new StringBuffer();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        sql.append(" SELECT\n" +
                "t_dvc_sample.TON_CONFIG as tonConfig\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_dvc_outbound ON t_con_main.ID=t_dvc_outbound.CONTRACT_ID\n" +
                "LEFT JOIN t_dvc_sample ON t_dvc_sample.id=t_dvc_outbound.SAMPLE_ID\n" +
                "WHERE 1=1\n" +
                "AND t_con_main.REGION_MANAGER_ID='"+userId+"'\n" +
                " AND t_dvc_outbound.TYPE=0 AND t_con_main.AUDIT_FLAG=1 AND HAVE_DELIVERY=1 AND t_con_main.DELIVERY_DATE  LIKE " + " '%" + sdf.format(new Date()) + "%'\n");
        return super.queryForJsonList(sql.toString());
    }
    public List<JSONObject> getDeviceType(String userId,String parentId,String parentName ) {
        StringBuffer sql = new StringBuffer();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        sql.append(" SELECT\n" +
                "t_dvc_sample.TON_CONFIG as tonConfig\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_dvc_outbound ON t_con_main.ID=t_dvc_outbound.CONTRACT_ID\n" +
                "LEFT JOIN t_dvc_sample ON t_dvc_sample.id=t_dvc_outbound.SAMPLE_ID\n" +
                "WHERE 1=1\n" +
                "AND t_con_main.REGION_MANAGER_ID='"+userId+"'\n" +
                " AND t_dvc_outbound.TYPE=0 AND t_con_main.AUDIT_FLAG=1 AND HAVE_DELIVERY=1 AND t_con_main.DELIVERY_DATE  LIKE " + " '%" + sdf.format(new Date()) + "%'\n");
        sql.append(" AND (t_con_main."+parentName+"='"+parentId+"' OR t_con_main."+parentName+" IS NULL)");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getRegionDeviceType(String userId, JSONObject target) {
        String beginMonth = target.getString("beginMonth");
        String middleMonth = target.getString("middleMonth");
        String endMonth = target.getString("endMonth");
        StringBuffer sql = new StringBuffer();
       /* String year = target.getString("year");
        if (beginMonth.length() < 2) {
            beginMonth = "0" + beginMonth;
        }
        if (middleMonth.length() < 2) {
            middleMonth = "0" + middleMonth;
        }
        if (endMonth.length() < 2) {
            endMonth = "0" + endMonth;
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        String year2 = sdf.format(new Date()).substring(0, 4);
        if ("11".equals(beginMonth)) {
            beginMonth = year + "-" + beginMonth;
            middleMonth = year + "-" + middleMonth;
            endMonth = String.valueOf(Integer.parseInt(year) + 1) + "-" + endMonth;
        } else if ("12".equals(beginMonth)) {
            beginMonth = year + "-" + beginMonth;
            middleMonth = String.valueOf(Integer.parseInt(year) + 1) + "-" + middleMonth;
            endMonth = String.valueOf(Integer.parseInt(year) + 1) + "-" + endMonth;
        } else if ("01".equals(beginMonth)) {
            beginMonth = year2 + "-" + beginMonth;
            middleMonth = year2 + "-" + middleMonth;
            endMonth = year2 + "-" + endMonth;
        } else {
            beginMonth = year + "-" + beginMonth;
            middleMonth = year + "-" + middleMonth;
            endMonth = year + "-" + endMonth;
        }*/

        sql.append(" SELECT\n" +
                "t_dvc_sample.TON_CONFIG as tonConfig\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_dvc_outbound ON t_con_main.ID=t_dvc_outbound.CONTRACT_ID\n" +
                "LEFT JOIN t_dvc_sample ON t_dvc_sample.id=t_dvc_outbound.SAMPLE_ID\n" +
                "WHERE t_con_main.REGION_MANAGER_ID=? AND t_dvc_outbound.TYPE=0 AND t_con_main.AUDIT_FLAG=1 AND HAVE_DELIVERY=1 AND (t_con_main.DELIVERY_DATE  LIKE " + " '" + beginMonth + "%' OR t_con_main.DELIVERY_DATE LIKE " + " '" + middleMonth + "%'" +
                " OR t_con_main.DELIVERY_DATE LIKE " + " '" + endMonth + "%')");
        return super.queryForJsonList(sql.toString(), new Object[]{userId});
    }

    public JSONObject getBigRegionDeviceType(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT IFNULL(sum(allnum.samll),0) AS small,IFNULL(sum(allnum.middle),0) AS middle,IFNULL(sum(allnum.big),0) big");
        sql.append(" FROM ( SELECT configInfo.tonconfig, CASE WHEN configInfo.tonconfig < 10 THEN count(configInfo.tonconfig < 10)");
        sql.append(" END AS samll,CASE WHEN configInfo.tonconfig BETWEEN 10 AND 30 THEN count( configInfo.tonconfig BETWEEN 10 AND 30 )");
        sql.append(" END AS middle, CASE WHEN configInfo.tonconfig > 30 THEN count(configInfo.tonconfig > 30) END AS big");
        sql.append(" FROM ( SELECT t_dvc_sample.TON_CONFIG AS tonconfig FROM t_con_main LEFT JOIN t_dvc_outbound ON t_con_main.ID = t_dvc_outbound.CONTRACT_ID");
        sql.append(" LEFT JOIN t_dvc_sample ON t_dvc_sample.id = t_dvc_outbound.SAMPLE_ID WHERE t_con_main.REGION_MANAGER_ID IN (");
        sql.append(" SELECT USER_ID FROM t_sys_user_2_region LEFT JOIN t_sys_user ON t_sys_user.id=t_sys_user_2_region.USER_ID");
        sql.append("  LEFT JOIN t_sys_role ON t_sys_role.id=t_sys_user.ROLE_ID");
        sql.append(" LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID");
        sql.append(" WHERE  t_sys_user_2_region.REGION_ID IN ( SELECT t_sys_user_2_region.REGION_ID");
        sql.append(" FROM t_sys_user_2_region WHERE t_sys_user_2_region.USER_ID =? )");
        sql.append(" AND t_sys_role_name.`NAME`=\"区域经理\" ) AND t_con_main.HAVE_DELIVERY=1 ) AS configInfo");
        sql.append(" GROUP BY configInfo.tonconfig ) AS allnum");
        return super.queryForJsonObject(sql.toString(), new Object[]{userId});

    }

    public List<JSONObject> getRegionManagerList(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "  t_sys_user.ID as userId,\n" +
                "  t_sys_user.`NAME` as userName,\n" +
                "\tt_sys_region.id AS regionId,\n" +
                "\tt_sys_region. NAME AS regionName,t_sys_user.ENTRY_DATE as entryDate\n" +
                "FROM\n" +
                "\tt_sys_user\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID = t_sys_user.id\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.id\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_role_name.`NAME` IN ('区域经理','区域组长')\n" +
                "AND t_sys_region.PARENT_ID = 0\n" +
                "AND t_sys_depart.DEL_FLAG = 0\n" +
                "AND t_sys_role.DEL_FLAG = 0\n" +
                "AND t_sys_user.JOB_STATE!=1");
        sql.append(condition);
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getBigRegionList(String userId, String companyId, String roleNames, String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" t_sys_company.ID AS companyId,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "\tt_sys_depart.ID AS departId,\n" +
                "\tt_sys_depart.`NAME` AS departName,\n" +
                "\tIFNULL(t_sys_role_name.`NAME`,'') AS roleName,\n" +
                "\tIFNULL(t_sys_role_name.`ID` ,'')AS roleId,\n" +
                "\tIFNULL(t_sys_user.`ID`,'') AS userId,\n" +
                "\tIFNULL(t_sys_user.`NAME`,'') AS userName");
        sql.append(" FROM  t_sys_depart");
        sql.append(" LEFT JOIN t_sys_company ON t_sys_company.ID = t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ROLE_ID = t_sys_role.ID");
        sql.append(" AND t_sys_user.JOB_STATE <> 1");
        sql.append(" AND t_sys_user.ID in(SELECT t_sys_user_2_region.USER_ID FROM t_sys_user_2_region WHERE \n" +
                "REGION_ID in (SELECT REGION_ID from t_sys_user_2_region WHERE  USER_ID='" + userId + "')\n" +
                ")");
        sql.append(" WHERE 1=1 AND t_sys_company.ID='" + companyId + "'");
        sql.append(" and t_sys_role_name.`NAME` in(" + roleNames + ")");
        sql.append(" AND t_sys_depart.DEL_FLAG = 0\n" +
                "AND t_sys_role.DEL_FLAG = 0 ");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition);
        }
        sql.append(
                " GROUP BY\n" +
                        "\tt_sys_company.ID,\n" +
                        "\tt_sys_depart.ID,\n" +
                        "\tt_sys_role.ID,\n" +
                        "\tt_sys_role_name.`ID`,\n" +
                        "\tt_sys_user.ID\n" +
                        "ORDER BY \n" +
                        " t_sys_depart.SORT_NO,\n" +
                        "t_sys_role.SORT_NO DESC\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getRegionMonth(String currentMonth) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT USER_ID as userId,");
        sql.append(" AREA_ID as regionId,");
        sql.append(" BEGIN_MONTH as beginMonth,");
        sql.append(" MIDDLE_MONTH as middleMonth,");
        sql.append(" END_MONTH AS endMonth");
        sql.append(" FROM");
        sql.append(" t_sys_region_achivements");
        sql.append(" WHERE ? =END_MONTH");
        //sql.append(" WHERE ? =BEGIN_MONTH");
        sql.append(" GROUP BY USER_ID,AREA_ID");
        sql.append(" ORDER BY CREATE_TIME DESC");
        return super.queryForJsonList(sql.toString(), new Object[]{currentMonth});
    }

    public List<JSONObject> getCompanyList() {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tID as id,\n" +
                "   `NAME` as name\n" +
                "FROM\n" +
                "\tt_sys_company\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND del_flag = 0\n" +
                "AND PARENT_ID = '201712161613432EAF27DBCCB7D0E6CA'\n" +
                "GROUP BY\n" +
                "\tt_sys_company.id\n" +
                "ORDER BY SORT_NO ");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getAllCheckUser(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" userId,userName,roleId,departId,entryDate,t_sys_role_name.`NAME` as roleName,t_sys_region.ID as regionId,companyId,t_sys_depart.`NAME` as departName,t_sys_company.`NAME` as companyName");
        sql.append(" FROM");
        sql.append(" v_sys_part_time_for_report");
        sql.append(" LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID");
        sql.append(" LEFT JOIN t_sys_company ON t_sys_company.id=v_sys_part_time_for_report.companyId");
        sql.append(" LEFT JOIN t_sys_depart ON t_sys_depart.id=v_sys_part_time_for_report.departId");
        sql.append(" LEFT JOIN t_sys_region ON t_sys_depart.`NAME`= t_sys_region.`NAME`");
        sql.append(" AND t_sys_region.COMPANY_ID = t_sys_depart.COMPANY_ID");
        sql.append(" where t_sys_role_name.name in(\"区域经理\",\"区域组长\",\"大区经理\",\"区域总监\",\"销售总监\",\"总经理\") AND (t_sys_region.DEL_FLAG=0 or t_sys_region.ID is null) AND v_sys_part_time_for_report.jobState!=1");
        sql.append(condition);
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getAllCheckUser2(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" userId,userName,roleId,departId,t_sys_role_name.`NAME` as roleName,t_sys_region.ID as regionId,companyId,t_sys_depart.`NAME` as departName,t_sys_company.`NAME` as companyName");
        sql.append(" FROM");
        sql.append(" v_sys_part_time_for_report");
        sql.append(" LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID");
        sql.append(" LEFT JOIN t_sys_company ON t_sys_company.id=v_sys_part_time_for_report.companyId");
        sql.append(" LEFT JOIN t_sys_depart ON t_sys_depart.id=v_sys_part_time_for_report.departId");
        sql.append(" LEFT JOIN t_sys_region ON t_sys_depart.`NAME`= t_sys_region.`NAME`");
        sql.append(" AND t_sys_region.COMPANY_ID = t_sys_depart.COMPANY_ID");
        sql.append(" where t_sys_role_name.name in(\"区域经理\",\"区域组长\",\"大区经理\",\"区域总监\",\"销售总监\",\"总经理\") AND (t_sys_region.DEL_FLAG=0 or t_sys_region.ID is null) ");
        sql.append(condition);
        return super.queryForJsonList(sql.toString());
    }

    public JSONObject getTargetByUserId(String userId, String regionId) {
        StringBuffer sql = new StringBuffer();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        //int currentMonth = Integer.parseInt(sdf.format(new Date()).split("-")[1]);
        String currentMonth=sdf.format(new Date());
        sql.append(" SELECT \n" +
                " SALE_TARGET_NUM AS targetNum\n " +
                "FROM t_sys_region_achivements\n");
        sql.append(" WHERE USER_ID=?  AND AREA_ID=? AND ? IN(BEGIN_MONTH,MIDDLE_MONTH,END_MONTH) ORDER BY CREATE_TIME DESC LIMIT 0,1");
        return super.queryForJsonObject(sql.toString(), new Object[]{userId, regionId, currentMonth});
    }

    public JSONObject getBigRegionIdByUserIdAndCompanyId(String userId, String departName) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_sys_region.id as regionId,\n" +
                "t_sys_region.`NAME` as regionName\n" +
                "FROM\n" +
                "\tt_sys_region\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_region.id = t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.id=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID=t_sys_depart.id\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.id=t_sys_depart.COMPANY_ID\n" +
                "WHERE t_sys_user.id= ? AND t_sys_region.name=?\n" +
                " AND t_sys_region.PARENT_ID=0");

        return super.queryForJsonObject(sql.toString(), new Object[]{userId, departName});
    }

    public List<JSONObject> getBigRegionByUserId(String userId, String compnayId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_sys_region.id AS regionId,\n" +
                "\tt_sys_region. NAME AS regionName,\n" +
                "t_sys_region.COMPANY_ID as companyId\n" +
                "FROM\n" +
                "\tt_sys_user\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID = t_sys_user.id\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.id\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "LEFT JOIN t_sys_user_2_role ON t_sys_user_2_role.USER_ID=t_sys_user.ID\n" +
                "WHERE\n" +
                "\tt_sys_region.ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user_2_region.REGION_ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tWHERE\n" +
                "\t\t\tREGION_ID IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tREGION_ID\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_sys_user_2_region\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tUSER_ID = ?\n" +
                "\t\t\t)\n" +
                "\t)\n" +
                "AND t_sys_region.COMPANY_ID = ?\n" +
                "AND t_sys_region.PARENT_ID = 0\n" +
                "AND t_sys_role.DEL_FLAG = 0\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "GROUP BY\n" +
                "\tt_sys_region.ID\n" +
                "ORDER BY\n" +
                "\n" +
                "\tt_sys_role.SORT_NO DESC");
        return super.queryForJsonList(sql.toString(), new Object[]{userId, compnayId});
    }

    //获取已交机的区域经理id
    public JSONObject getRegionManagerId(String sampleId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_con_main.REGION_MANAGER_ID as regionManagerId," +
                "  t_con_main.SELLER_COMPANY_ID as companyId\n" +
                "FROM\n" +
                "\tt_con_main\n" +
                "LEFT JOIN t_dvc_outbound ON t_dvc_outbound.CONTRACT_ID=t_con_main.ID\n" +
                "WHERE t_dvc_outbound.SAMPLE_ID=? AND t_dvc_outbound.TYPE=0");
        return super.queryForJsonObject(sql.toString(), new Object[]{sampleId});
    }

    //获取交机后需要修改人的id
    public List<JSONObject> getUserListByRegionManagerId(String companyId, String regionManagerId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tuserId,\n" +
                "\tuserName,\n" +
                "\troleId,\n" +
                "\tdepartId,\n" +
                "\tt_sys_role_name.`NAME` AS roleName,\n" +
                "\tt_sys_region.ID AS regionId,\n" +
                "\tcompanyId,\n" +
                "\tt_sys_depart.`NAME` AS departName,\n" +
                "\tt_sys_company.`NAME` AS companyName\n" +
                "FROM\n" +
                "\tv_sys_part_time_for_report\n" +
                "LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.id = v_sys_part_time_for_report.companyId\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.id = v_sys_part_time_for_report.departId\n" +
                "LEFT JOIN t_sys_region ON t_sys_depart.`NAME` = t_sys_region.`NAME`\n" +
                "AND t_sys_region.COMPANY_ID = t_sys_depart.COMPANY_ID\n" +
                "WHERE\n" +
                "\tt_sys_role_name. NAME IN (\n" +
                "\t\t\"大区经理\",\n" +
                "\t\t\"区域总监\",\n" +
                "\t\t\"销售总监\",\n" +
                "\t\t\"总经理\"\n" +
                "\t)\n" +
                "AND (\n" +
                "\tt_sys_region.DEL_FLAG = 0\n" +
                "\tOR t_sys_region.ID IS NULL\n" +
                ")\n");
        sql.append(" AND companyId =?");
        sql.append(" AND v_sys_part_time_for_report.jobState != 1 ");
        sql.append(" AND userId IN (\n" +
                "\tSELECT\n" +
                "\t\tUSER_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_user_2_region\n" +
                "\tWHERE\n" +
                "\t\tREGION_ID IN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tREGION_ID\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_user_2_region\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tUSER_ID = ?" +
                "\t\t)\n" +
                ") OR userId=?");

        return super.queryForJsonList(sql.toString(), new Object[]{companyId, regionManagerId, regionManagerId});
    }

    public List<JSONObject> getAllParent(String bigRegionId, String companyId, String roleName) {
        StringBuffer sql = new StringBuffer();
        if (!roleName.equals("总经理")) {
            sql.append("SELECT\n" +
                    " v_sys_part_time_for_report.userId AS userId,\n" +
                    "v_sys_part_time_for_report.userName AS userName,\n" +
                    "t_sys_role_name.`NAME` AS roleName\n" +
                    "FROM\n" +
                    "v_sys_part_time_for_report\n" +
                    "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=v_sys_part_time_for_report.userId\n" +
                    "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=v_sys_part_time_for_report.roleNameId\n" +
                    "WHERE 1=1\n" +
                    "AND '" + bigRegionId + "' in(t_sys_user_2_region.REGION_ID)\n" +
                    "AND v_sys_part_time_for_report.jobState<>1\n" +
                    "AND v_sys_part_time_for_report.companyId='" + companyId + "'\n" +
                    "AND t_sys_role_name.`NAME` = '" + roleName + "'");
        }else {
            sql.append("SELECT\n" +
                    "* \n" +
                    "FROM\n" +
                    "(\n" +
                    "SELECT\n" +
                    "CASE\n" +
                    "WHEN\n" +
                    " v_sys_part_time_for_report.userId='2017121620304867AB2903435C97759D' \n" +
                    "AND v_sys_part_time_for_report.companyId='20180426083647FCBC7D08C7B14E496E'\n" +
                    "AND t_sys_role_name.`NAME`='总经理'\n" +
                    "THEN NULL\n" +
                    "ELSE v_sys_part_time_for_report.userId\n" +
                    "END userId,\n" +
                    "v_sys_part_time_for_report.userName AS userName,\n" +
                    "t_sys_role_name.`NAME` AS roleName\n" +
                    "\n" +
                    "FROM\n" +
                    "v_sys_part_time_for_report\n" +
                    "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=v_sys_part_time_for_report.userId\n" +
                    "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=v_sys_part_time_for_report.roleNameId\n" +
                    "WHERE 1=1\n" +
                    "AND '" + bigRegionId + "' in(t_sys_user_2_region.REGION_ID)\n" +
                    "AND v_sys_part_time_for_report.jobState<>1\n" +
                    "AND v_sys_part_time_for_report.companyId='" + companyId + "'\n" +
                    "AND t_sys_role_name.`NAME` = '总经理'\n" +
                    ")AS sysMain\n" +
                    "WHERE 1=1\n" +
                    "AND sysMain.userId IS NOT NULL");
        }
        return super.queryForJsonList(sql.toString());

    }

    public JSONObject getMainRoleNameByUserId(String userId,String companyId,String roleName) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_role_name.`NAME` AS roleName,\n" +
                "t_sys_depart.COMPANY_ID AS companyId\n" +
                "FROM\n" +
                "t_sys_user\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "WHERE 1=1\n" +
                "AND  t_sys_user.ID='"+userId+"'\n" +
                "AND t_sys_depart.COMPANY_ID='"+companyId+"'\n" +
                "AND t_sys_role_name.`NAME`='"+roleName+"'");
        return super.queryForJsonObject(sql.toString());
    }



}
